﻿Imports System.IO

Public Class Syncronizar
    Dim dtSync As DataTable


    Private Sub Syncronizar_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Comprime_Imagen(btnInfo.Image, 32, 32)
        Comprime_Imagen(btnPedidos.Image, 32, 32)

        If File.Exists(ruta_programa & "\Info.xml") Then
            dsInfo = New DataSet
            dsInfo.ReadXml(ruta_programa & "\Info.xml")

            Carga_Datos()
            Dim validado As Boolean = True

            If dsInfo.Tables("CLIENTES").Select("CCLIE LIKE 'NC%'").Length > 0 Then
                Dim ValClie As New Validacion_Clientes
                validado = ValClie.Mostrar()
            End If

            If Not validado Then
                Me.btnImagenes.Enabled = False
                Me.btnInfo.Enabled = False
                Me.btnPedidos.Enabled = False
            Else
                Me.btnImagenes.Enabled = True
                Me.btnInfo.Enabled = True
                Me.btnPedidos.Enabled = True

                If dsInfo.Tables("CLIENTES").Select("CCLIE LIKE 'NC%'").Length > 0 Then
                    Me.Show()
                    Me.Refresh()
                    Me.BringToFront()
                    btnInfo_Click(Nothing, Nothing)
                End If
            End If
        End If

        'iniTable()
    End Sub

    'Private Sub iniTable()
    '    dtSync = New DataTable
    '    dtSync.Columns.Add("Descripción", GetType(String))
    '    dtSync.Columns.Add("X", GetType(Image))
    'End Sub

    Private Sub btnInfo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInfo.Click
        dgSync.DataSource = Nothing
        dgSync.Refresh()

        dtSync = New DataTable
        dtSync.Columns.Add("Descripción", GetType(String))
        dtSync.Columns.Add("X", GetType(Image))

        dsInfo = New DataSet

        Dim tam As Integer = 20

        Dim bmp As New Bitmap(tam, tam)
        Dim g As Graphics = Graphics.FromImage(bmp)
        g.Clear(Color.White)

        Dim bmpError As New Bitmap(tam, tam)
        Dim gError As Graphics = Graphics.FromImage(bmpError)
        gError.Clear(Color.White)

        Dim fila As DataRow

        fila = dtSync.NewRow
        fila(0) = "Clientes"
        fila(1) = New Bitmap(bmp)
        dtSync.Rows.Add(fila)

        fila = dtSync.NewRow
        fila(0) = "Productos"
        fila(1) = New Bitmap(bmp)
        dtSync.Rows.Add(fila)

        fila = dtSync.NewRow
        fila(0) = "Politicas"
        fila(1) = New Bitmap(bmp)
        dtSync.Rows.Add(fila)

        fila = dtSync.NewRow
        fila(0) = "Cuentas"
        fila(1) = New Bitmap(bmp)
        dtSync.Rows.Add(fila)

        fila = dtSync.NewRow
        fila(0) = "Impuestos"
        fila(1) = New Bitmap(bmp)
        dtSync.Rows.Add(fila)

        dgSync.DataSource = dtSync
        dgSync.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill

        Dim cString As String = Get_Connection()

        Dim con As New Conexion_SQL(cString)

        'If con.ConnectDataBase(cString) Then
        '    msg.Mostrar("Conexion Exitosa", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
        'Else
        '    msg.Mostrar("Conexion Fallida", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
        '    Exit Sub
        'End If

        dgSync.DataSource = dtSync
        dgSync.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        g.DrawImage(My.Resources.Good_or_Tick, 0, 0, tam, tam)

        gError.DrawImage(My.Resources._Error, 0, 0, tam, tam)

        dsInfo = New DataSet

        Dim cad As String

        Try
            '------CLIENTES------
            cad = "SELECT CCLIE, NOMBRE, RFC, DIR, COLONIA, POB, CODIGO, TELEFONO, EMAIL, LTRIM(CLASIFIC) AS CLASIFIC , DIAS_CRE, SALDO, LIM_CRED, CAMLIBRE2 AS DIA, CAST(VEND AS BIGINT) AS VEND "
            cad += " FROM CLIE" & Valor_Config("FIJOS", "Empresa")
            cad += " WHERE STATUS='A'  AND (VEND=" & Val(Valor_Config("EMPRESA", "ID del Vendedor")).ToString & " OR CCLIE='MOSTR') "
            cad += " GROUP BY CCLIE, NOMBRE, RFC, DIR, COLONIA, POB, CODIGO, TELEFONO, EMAIL, LTRIM(CLASIFIC), DIAS_CRE, SALDO, LIM_CRED, CAMLIBRE2, VEND "
            cad += "ORDER BY NOMBRE"

            Dim dtClientes As New DataTable("CLIENTES")
            con.OpenQueryIntoDataTable(dtClientes, cad)

            dsInfo.Tables.Add(dtClientes)

            dtSync.Rows(0)(1) = New Bitmap(bmp)

        Catch ex As Exception
            dtSync.Rows(0)(1) = New Bitmap(bmpError)
        End Try
        dgSync.Refresh()

        Try
            '------PRODUCTOS------

            cad = "SELECT CLV_ART as CLAVE, DESCR, PRECIO1, PRECIO2,COSTO_PROM,EXIST, UNI_MED as UNIDAD, LIN_PROD AS LINEA,CVEESQIMP"
            cad += " FROM INVE" & Valor_Config("FIJOS", "Empresa")
            cad += " WHERE STATUS='A' AND TIPO_ELE='I' AND EXIST>0 "
            cad += "GROUP BY CLV_ART, DESCR, UNI_MED, LIN_PROD, PRECIO1, PRECIO2,COSTO_PROM,EXIST,CVEESQIMP "
            cad += "ORDER BY DESCR"


            Dim dtProductos As New DataTable("PRODUCTOS")
            con.OpenQueryIntoDataTable(dtProductos, cad)

            dsInfo.Tables.Add(dtProductos)

            dtSync.Rows(1)(1) = New Bitmap(bmp)
        Catch ex As Exception
            dtSync.Rows(1)(1) = New Bitmap(bmpError)
        End Try

        dgSync.Refresh()

        Try
            '------POLITICAS------

            cad = "SELECT NUM_REG, DESCR, cast(CLV_INI as bigint) as CLV_INI, cast(CLV_FIN as bigint) as CLV_FIN, CLIE, LISTA_PREC"
            cad += " FROM POLI" & Valor_Config("FIJOS", "Empresa")
            cad += " WHERE ST='A'"

            'cad = "SELECT NUM_REG, DESCR, CLV_INI, CLV_FIN, CLIE, LISTA_PREC"
            'cad += " FROM POLI" & Valor_Config("FIJOS", "Empresa")
            'cad += " WHERE ST='A'"

            Dim dtPoliticas As New DataTable("POLITICAS")
            con.OpenQueryIntoDataTable(dtPoliticas, cad)

            dsInfo.Tables.Add(dtPoliticas)

            dtSync.Rows(2)(1) = New Bitmap(bmp)
        Catch ex As Exception
            dtSync.Rows(2)(1) = New Bitmap(bmpError)
        End Try

        dgSync.Refresh()

        Try
            '------CUENTAS------

            cad = "SELECT R.CCLIE, R.REFER, cast(C.IMPORTE as money) AS IMPORTE, R.SALDO, C.FECHA_APLI AS FECHA FROM "
            cad += "(SELECT REFER, CCLIE, SUM(IMPORTE) AS SALDO FROM "
            cad += "(SELECT REFER, CCLIE, TIPO_MOV, (CASE WHEN TIPO_MOV=1 THEN cast(IMPORTE as money) ELSE cast(IMPORTE*(-1) as money) END) AS IMPORTE "
            cad += "FROM CUEN" & Valor_Config("FIJOS", "Empresa") & " WHERE TIPO_MOV IN (1,10,11,12)) RES "
            cad += "GROUP BY REFER, CCLIE) R "
            cad += "INNER JOIN CUEN" & Valor_Config("FIJOS", "Empresa") & " C ON R.REFER=C.REFER AND R.SALDO>0.1 AND TIPO_MOV=1 "
            cad += "ORDER BY C.FECHA, R.CCLIE, R.REFER "

            Dim dtCuentas As New DataTable("CUENTAS")
            con.OpenQueryIntoDataTable(dtCuentas, cad)

            dsInfo.Tables.Add(dtCuentas)

            dtSync.Rows(3)(1) = New Bitmap(bmp)
        Catch ex As Exception
            dtSync.Rows(3)(1) = New Bitmap(bmpError)
        End Try

        dgSync.Refresh()


        Try
            '------IMPUESTOS------

            cad = "SELECT CVEESQIMPU, DESCRIPESQ, IMPUESTO4 FROM IMPU" & Valor_Config("FIJOS", "Empresa")

            Dim dtImpuestos As New DataTable("IMPUESTOS")
            con.OpenQueryIntoDataTable(dtImpuestos, cad)

            dsInfo.Tables.Add(dtImpuestos)

            dtSync.Rows(4)(1) = New Bitmap(bmp)
        Catch ex As Exception
            dtSync.Rows(4)(1) = New Bitmap(bmpError)
        End Try

        dgSync.Refresh()

        If File.Exists(ruta_programa & "\Info.xml") Then
            File.Delete(ruta_programa & "\Info.xml")
        End If

        If File.Exists(ruta_programa & "\Datos.xml") Then
            File.Copy(ruta_programa & "\Datos.xml", ruta_programa & "\Respaldo\Datos " & Format(Now, "ddMMyyyyHHmmss") & ".xml")
        End If

        dsInfo.WriteXml(ruta_programa & "\Info.xml", XmlWriteMode.WriteSchema)
    End Sub

    Private Sub btnPedidos_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPedidos.Click
        dgSync.DataSource = Nothing
        dgSync.Refresh()

        Carga_Datos()

        dsInfo = New DataSet
        dsInfo.ReadXml(ruta_programa & "\Info.xml")

        Dim cString As String = Get_Connection()
        Dim con As New Conexion_SQL(cString)

        Dim cad As String = ""

        'Dim dtSync As New DataTable
        dtSync = New DataTable
        dtSync.Columns.Add("REFER", GetType(String))
        dtSync.Columns.Add("CCLIE", GetType(String))
        dtSync.Columns.Add("TOTAL", GetType(Double))
        dtSync.Columns.Add("X", GetType(Image))

        Dim tam As Integer = 20
        Dim bmp As New Bitmap(tam, tam)
        Dim g As Graphics = Graphics.FromImage(bmp)
        g.Clear(Color.White)
        g.DrawImage(My.Resources.Good_or_Tick, 0, 0, tam, tam)

        dgSync.DataSource = dtSync
        dgSync.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        dgSync.Columns("TOTAL").DefaultCellStyle.Format = "$ ###,###,##0.00"

        For Each factura As DataRow In dsData.Tables("FACTURAS").Select("STATUS=TRUE")
            Dim uReg As Integer = con.QueryGetFieldTmpCnn("select NUM_REGS from FA0T" & Valor_Config("FIJOS", "Empresa"))
            Dim uFolio As Integer = con.QueryGetFieldTmpCnn("select ULT_DOCP from FA0T" & Valor_Config("FIJOS", "Empresa"))
            Dim fProductos As DataRow() = dsData.Tables("PRODUCTOS").Select("REFER='" & factura("REFER") & "' AND STATUS=TRUE")
            Dim RFC As String = CType(dsInfo.Tables("CLIENTES").Select("CCLIE='" & factura("CCLIE") & "'")(0), DataRow)("RFC")

            cad = "INSERT INTO FACT" & Valor_Config("FIJOS", "Empresa")
            cad += vbCrLf & "(NUM_REG,TIP_REG,TIP_DOC,CVE_DOC,CVE_CLPV,STATUS,CVE_VEND,FECHA_DOC,FECHA_ENT,FECHA_VEN,CAN_TOT,"
            cad += vbCrLf & "IMP_TOT1,IMP_TOT2,DES_TOT,DES_FIN,COM_TOT,OBS_ENVI,OBS_FACT,OBS_CLIE,NUM_PART,NUM_ALMA,BLOQ,"
            cad += vbCrLf & "NUM_MONED,TIPCAMB,NOPAGOS,COSTOFLET,IMPUEFLET,RETENFLET,FECHA_ENV,FECHAELAB,USUARIO,IMP_TOT3,"
            cad += vbCrLf & "IMP_TOT4,PRIMERPAGO,RFC,CTLPOL,ESCFD,AUTORIZA,FOLIO) VALUES"

            cad += vbCrLf & "('" & uReg + 1 & "',1,'P','" & factura("REFER") & "','" & factura("CCLIE") & "','E','" & agrega_espacios(factura("USUARIO"), 5) & "',"
            cad += vbCrLf & "convert(datetime,'" & Format(CDate(factura("FECHA")), "yyyy-MM-dd") & "',111),"
            cad += vbCrLf & "convert(datetime,'" & Format(CDate(factura("ENTREGA")), "yyyy-MM-dd") & "',111),"
            cad += vbCrLf & "convert(datetime,'" & Format(CDate(factura("FECHA")), "yyyy-MM-dd") & "',111), " & factura("SUBTOTAL") & ", 0, 0, 0, 0, 0,"
            cad += vbCrLf & "0,0,0," & fProductos.Length & ",1,-1,1,1,0,0,0,0,"
            cad += vbCrLf & "convert(datetime,'" & Format(CDate(factura("ENTREGA")), "yyyy-MM-dd") & "',111),"
            cad += vbCrLf & "convert(datetime,'" & Format(CDate(factura("FECHA")), "yyyy-MM-dd") & "',111), " & factura("USUARIO") & ", 0,"
            cad += vbCrLf & "" & factura("IVA") & ", 0, "
            cad += vbCrLf & "'" & RFC & "',0,'N',0," & uFolio + 1 & ")"

            con.ExecuteSQL(cad)
            cad = "UPDATE FA0T" & Valor_Config("FIJOS", "Empresa") & " SET NUM_REGS=" & uReg + 1 & ",ULT_DOCP=" & uFolio + 1 & ",ULTFECHP=convert(datetime,'" & Format(CDate(factura("FECHA")), "yyyy-MM-dd") & "',111)"
            con.ExecuteSQL(cad)

            For Each producto As DataRow In fProductos
                uReg = con.QueryGetFieldTmpCnn("select NUM_REGS from FA0T" & Valor_Config("FIJOS", "Empresa"))
                Dim tIVA As Integer = CType(dsInfo.Tables("PRODUCTOS").Select("CLAVE='" & producto("ARTICULO") & "'")(0), DataRow)("CVEESQIMP")
                Dim cveIVA As Integer = CType(dsInfo.Tables("IMPUESTOS").Select("CVEESQIMPU=" & tIVA)(0), DataRow)("IMPUESTO4")

                cad = "INSERT INTO FA0TY" & Val(Valor_Config("FIJOS", "Empresa"))
                cad += vbCrLf & "(NUM_REG,TIP_REG,TIP_DOC,CVE_DOC,CVE_ART,CANT,PXS,PREC,COST,IMPU1,IMPU2,DESC1,DESC2,DESC3,COMI,PAR_ANT,"
                cad += vbCrLf & "PAR_SIG,PAR_ASIG,ACT_INV,NUM_ALM,TIP_CAM,REG_SERIE,REG_OBS,APAR,UNI_VENTA,TIPO_PROD,U4SEC,MONFLETE,IMPU3,"
                cad += vbCrLf & "IMPU4,IMP1APLA,IMP2APLA,IMP3APLA,IMP4APLA,TOTIMP1,TOTIMP2,TOTIMP3,TOTIMP4,TIPO_PRODT) VALUES"

                cad += vbCrLf & "('" & uReg + 1 & "',2,'P','" & factura("REFER") & "','" & producto("ARTICULO") & "'," & producto("CANTIDAD") & ","
                cad += vbCrLf & producto("CANTIDAD") & "," & producto("PRECIO") & ",0,0,0,0,0,0,0,0,0,0,'A',1,1,0,0,0,'pz','I',0,0,0,"
                cad += vbCrLf & cveIVA & ",0,0,0," & tIVA & ",0,0,0," & producto("CANTIDAD") * producto("IVA") & ",'I')"

                con.ExecuteSQL(cad)
                cad = "UPDATE FA0T" & Valor_Config("FIJOS", "Empresa") & " SET NUM_REGS=" & uReg + 1
                con.ExecuteSQL(cad)

                producto("STATUS") = False
            Next

            con.ExecuteSQL("UPDATE FA0TY" & Val(Valor_Config("FIJOS", "Empresa")) & " SET TIP_ANT='', DOC_ANT='', TIP_SIG='', DOC_SIG='', POLITS='' WHERE CVE_DOC='" & factura("REFER") & "'")
            con.ExecuteSQL("UPDATE FACT" & Valor_Config("FIJOS", "Empresa") & " SET MOSTRA='', CVE_PEDI='', OBS_COND='', ACT_CXC='A', ACT_COI='', CVE_CONS='', STRNOGUIA='', STRMODOENV='', DOCANTSIG='', CVE_ZONA='', TIPO_FLET='', SERIE='', TIPDOCANT='', AUTOANIO='' WHERE CVE_DOC='" & factura("REFER") & "'")


            Dim fSync As DataRow = dtSync.NewRow
            fSync(0) = factura("REFER")
            fSync(1) = factura("CCLIE")
            fSync(2) = factura("TOTAL")
            fSync(3) = New Bitmap(bmp)
            dtSync.Rows.Add(fSync)

            dgSync.Refresh()

            factura("STATUS") = False
        Next

        Guarda_Datos()

        msg.Mostrar("Los pedidos han sido guardados con éxito!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Function agrega_espacios(ByVal cad As String, ByVal cantidad As Integer) As String
        For i As Integer = cad.Length To cantidad - 1
            cad = " " & cad
        Next
        Return cad
    End Function

    Private Sub btnImagenes_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImagenes.Click
        dgSync.DataSource = Nothing
        dgSync.Refresh()

        dtSync = New DataTable
        dtSync.Columns.Add("Archivo", GetType(String))
        dtSync.Columns.Add("X", GetType(Image))

        dgSync.DataSource = dtSync
        dgSync.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells

        Dim tam As Integer = 20
        Dim bmp As New Bitmap(tam, tam)
        Dim g As Graphics = Graphics.FromImage(bmp)
        g.Clear(Color.White)
        g.DrawImage(My.Resources.check, 0, 0, tam, tam)


        For Each archivo As String In Directory.GetFiles(Valor_Config("EMPRESA", "Ruta Imagenes"))
            Dim nombre As String = archivo.Substring(archivo.LastIndexOf("\") + 1)

            If File.Exists(ruta_programa & "\Fotos\" & nombre) Then File.Delete(ruta_programa & "\Fotos\" & nombre)


            File.Copy(archivo, ruta_programa & "\Fotos\" & nombre)

            Dim fila As DataRow = dtSync.NewRow
            fila(0) = nombre
            fila(1) = New Bitmap(bmp)
            dtSync.Rows.Add(fila)

            dgSync.FirstDisplayedCell = dgSync.Rows(dgSync.RowCount - 1).Cells(0)

            dgSync.Refresh()

            Application.DoEvents()
        Next

        msg.Mostrar("Las imágenes han sido guardadas con éxito!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Sub dgSync_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgSync.CellContentClick

    End Sub
End Class